package com.xisoft.generator.handle;

import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.xisoft.common.pubEnv.PubEnvUtil;
import com.xisoft.common.pubEnv.StringUtil;
import com.xisoft.generator.entity.BaseModel;
import com.xisoft.generator.entity.Column;
import com.xisoft.generator.entity.Table;
import com.xisoft.generator.mapper.SysGeneratorMapper;
import com.xisoft.generator.utils.ProcessorException;
import com.xisoft.utils.SpringHelper;

import java.util.List;
import java.util.concurrent.atomic.AtomicReference;


/**
 * @author xzh
 * @Date 2021/8/16
 * @description 数据库处理器：
 * 注意：本内容仅限于上海熙软科技有限公司内部，传阅，禁止外泄以及用于其他商业目的
 */
public class DBHandler implements Handler
{


    @Override
    public void handle(BaseModel model, HandlerChain chain)
    {
        StringBuilder sql1 = new StringBuilder();
        sql1.append(" SELECT t.table_name AS \"dname\",");
        sql1.append(" replace(initcap(lower(substr(t.table_name, instr(t.table_name, '_')))), '_', '') AS \"vname\",");
        sql1.append(" t.table_type AS \"table_type\",");
        sql1.append(" nvl(t.comments, '.') AS \"comments\"");
        sql1.append(" FROM user_tab_comments t");
        sql1.append(" WHERE (t.table_name = '");
        sql1.append(model.tablename.toLowerCase());
        sql1.append("' OR t.table_name = '");
        sql1.append(model.tablename.toUpperCase());
        sql1.append("') ");


        StringBuilder sql2 = new StringBuilder();
        sql2.append(" SELECT t.column_name \"dname\",");
        sql2.append(" lower(t.column_name) \"vname\",");
        sql2.append(" (CASE WHEN substr(t.data_type, 1, 9) = 'TIMESTAMP' ");
        sql2.append(" THEN substr(t.data_type, 1, 9) ");
        sql2.append(" ELSE t.data_type END) AS \"data_type\",");
        sql2.append(" (CASE WHEN c.comments IS NULL THEN ''");
        sql2.append(" WHEN instr(c.comments, '##', 1, 1) IS NOT NULL");
        sql2.append(" AND instr(c.comments, '##', 1, 1) != 0 THEN");
        sql2.append(" TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '##', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" WHEN instr(c.comments, '##', 1, 1) IS NOT NULL");
        sql2.append(" AND instr(c.comments, '##', 1, 1) != 0 THEN");
        sql2.append(" TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '##', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" WHEN instr(c.comments, '(', 1, 1) IS NOT NULL");
        sql2.append(" AND instr(c.comments, '(', 1, 1) != 0 THEN");
        sql2.append(" TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '(', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" WHEN instr(c.comments, '锛�', 1, 1) IS NOT NULL ");
        sql2.append(" AND instr(c.comments, '锛�', 1, 1) != 0 ");
        sql2.append(" THEN TRIM(replace(substr(c.comments, 1, ");
        sql2.append("     instr(c.comments, '锛�', 1, 1) - 1), CHR(10), ''))");
        sql2.append(" ELSE TRIM(replace(c.comments, CHR(10), '')) END) AS \"comments\",");
        sql2.append(" (CASE WHEN t.data_type = 'DATE' OR substr(t.data_type, 1, 9) ");
        sql2.append("     = 'TIMESTAMP' THEN 30");
        sql2.append(" WHEN t.data_type = 'NUMBER' AND t.data_scale = 0 ");
        sql2.append(" THEN t.data_precision");
        sql2.append(" WHEN t.data_type = 'NUMBER'");
        sql2.append(" AND t.data_scale > 0 ");
        sql2.append(" THEN( t.data_precision + t.data_scale + 1 )");
        sql2.append(" WHEN t.data_length > 1 ");
        sql2.append(" THEN t.data_length / 2");
        sql2.append(" ELSE t.data_length END) AS \"data_length\",");
        sql2.append(" (CASE WHEN t.data_type = 'NUMBER' AND t.data_scale = 0 ");
        sql2.append(" THEN ( lpad('9', t.data_precision, '9') )");
        sql2.append(" WHEN t.data_type = 'NUMBER' AND t.data_scale > 0 ");
        sql2.append(" THEN ( lpad('9', t.data_precision, '9') || ");
        sql2.append("     '.' || rpad('9', t.data_scale, '9') )");
        sql2.append(" END) AS \"data_max\",");
        sql2.append(" (CASE WHEN t.data_type = 'NUMBER'");
        sql2.append(" AND t.data_scale > 0 THEN ( '0,000.' || rpad('0', t.data_scale, '0') )");
        sql2.append(" END) AS \"data_format\" ,");
        sql2.append(" t.data_precision \"data_precision\",");
        sql2.append(" to_number(t.data_scale) AS \"data_scale\",");
        sql2.append(" t.nullable AS \"nullable\"");
        sql2.append(" FROM");
        sql2.append(" user_tab_cols       t,");
        sql2.append(" user_col_comments   c");
        sql2.append(" WHERE 1 = 1");
        sql2.append(" AND t.table_name = c.table_name");
        sql2.append(" AND t.column_name = c.column_name");
        sql2.append(" AND (t.table_name = '");
        sql2.append(model.tablename.toLowerCase());
        sql2.append("' OR  t.table_name = '");
        sql2.append(model.tablename.toUpperCase());
        sql2.append("') ");

        StringBuilder sql3 = new StringBuilder();
        sql3.append(" SELECT a.column_name AS \"column_name\"");
        sql3.append(" FROM");
        sql3.append("  user_cons_columns   a,");
        sql3.append("  user_constraints    b");
        sql3.append(" WHERE a.constraint_name = b.constraint_name");
        sql3.append(" AND b.constraint_type = 'P'");
        sql3.append(" AND (a.table_name = '");
        sql3.append(model.tablename.toLowerCase());
        sql3.append("' OR a.table_name = '");
        sql3.append(model.tablename.toUpperCase());
        sql3.append("') ");

        handleTable(model, chain, StringUtil.toString(sql1), StringUtil.toString(sql2), StringUtil.toString(sql3));
    }

    private void handleTable(BaseModel model, HandlerChain chain, String sql1, String sql2, String sql3)
    {
        AtomicReference< Table > table = new AtomicReference<>();
        try
        {
            // List< Table > tableList = Db.use().query(sql1, Table.class);
            List< Table > tableList = SpringHelper.getBean(SysGeneratorMapper.class).getTableInfo(sql1);
            if (PubEnvUtil.getSize(tableList) < 1)
            { throw new ProcessorException("您输入的表名 【" + model.tablename + "】，在Schema【"+Db.use().getConnection().getSchema()+"】数据源中不存在"); }
            table.set(tableList.get(0));
            AtomicReference< String > primaryKey = new AtomicReference<>();
            // List< Entity > primaryKeyList = Db.use().query(sql3);
            List< Entity > primaryKeyList = SpringHelper.getBean(SysGeneratorMapper.class).getPrimaryKey(sql3);
            if (PubEnvUtil.getSize(primaryKeyList) > 0)
            { primaryKey.set(StringUtil.valueOfZero(primaryKeyList.get(0).get("column_name"))); }
            List< Column > columnList = Db.use().query(sql2, Column.class);
            // List< Column > columnList = SpringHelper.getBean(SysGeneratorMapper.class).getColumnList(sql2);
            columnList.forEach(col ->
                               {
                                   if (!Column.ignoreColumns.toLowerCase().contains(col.getvName().toLowerCase()))
                                   {
                                       col.setPrimary(false);
                                       if (PubEnvUtil.equals(primaryKey.get(), col.dName))
                                       { col.setPrimary(true); }
                                       col.setVtypeByDType();
                                       col.setXmlTypeByDtype();
                                       col.setCOMMENTS(StringUtil.valueOfNull(col.getCOMMENTS()));
                                       col.setUNICODE_COMMENTS(StringUtil.chinaToUnicode(col.getCOMMENTS()));
                                       table.get().getColumns().add(col);
                                   }
                               });
        }
        catch (Exception e)
        {
            throw new RuntimeException(e);
        }
        model.setTable(table.get());
        chain.handle(model);
    }
}
